﻿--获取订单(分配网站跟进人员时用)
CREATE PROCEDURE [dbo].[proc_Project_Getlist_AllotService]
	(
		@CompanyId int,
		@DepId int,
		@StateId int,
		@TypeId int,
		@WayId int,
		@ProcessId int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@cusflag int --1按客服名查，0按客户名查
	)
AS
Begin
	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepId
	
	Declare @cSql varchar(4000)

Set @cSql = 'O.OId=List1.OrderId And ExCompanyId='+Convert(varchar,@CompanyId)+' And NOE_Flag=0 And List1.CreateDate>=@sRq And List1.CreateDate<=@eRq'

If @DepId<>0
	Set @cSql = @cSql +' And DepId in(Select Id From Department Where Left(bmbh,Len('''+@bmbh_T+'''))='''+@bmbh_T+''')'

If @StateId>0
	Set @cSql = @cSql + ' And StateId='+Convert(varchar(10),@StateId)
Else
	Set @cSql = @cSql + ' And StateId in (3,4,5) '
	
If @TypeId<>0
	Set @cSql = @cSql + ' And TypeId='+Convert(varchar(10),@TypeId)

If @WayId<>0
	Set @cSql = @cSql + ' And WayId='+Convert(varchar(10),@WayId)

If @ProcessId<>0
	Set @cSql = @cSql + ' And ProcessId='+Convert(varchar(10),@ProcessId)

If @Stext<>''
	if @cusflag=0
		Set @cSql = @cSql + ' And (CusName Like ''%'+@Stext+'%'' )'
	else
		Set @cSql = @cSql + ' And (GenJinKfName Like ''%'+@Stext+'%'')'

Set @cSql='
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,'''+@StartDate+' 00:00:00'')
	Set @eRq = Convert(Datetime,'''+@EndDate+' 23:59:59'')

	;With List1 As (
		Select Distinct OrderId,ProductName,CreateDate From Project
		Where CreateDate>=@sRq And (CompanyId='+Convert(varchar(10),@CompanyId)+' Or MoveCompanyId='+Convert(varchar(10),@CompanyId)+
		') And NOE_Flag=0 And ProductId In(Select Id from Product Where flag=1) And IsHedge=0
	),
	List As(Select ROW_NUMBER() OVER (Order By GenJinKfAccount,List1.CreateDate Desc,Id Desc) As Row
		,Id
		,OId
		,CusId --添加查询CusId,2015.08.21 lht
		,CusName
		,TypeId
		,TypeName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,List1.CreateDate
		,IsNull(YingShouPrice,0) as YingShouPrice
		,IsNull(ShiShouPrice,0) as ShiShouPrice
		,PaymentDescription
		,CompanyId
		,ExCompanyId
		,IsGreen
		,StateId
		,GenJinKfName
		,GenJinKfAccount
		,GenJinKfDepId
		,List1.ProductName
		--,Isnull((select Pizhu from Customer_PiZhu where CustomerId=CusId),'''') as Pizhu --批注内容字段 2015.08.20 
		--,Isnull((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
		From [Order] O,List1
		Where '+@cSql+'
	)

	Select *,
	IsNull((Select Title From Order_State Where Id=list.StateId),''---'') As StateName,
	(Select Count(0) From list) As RecordCount
	From list
	Where Row Between '+Convert(varchar,@StartIndex)+' And '+Convert(varchar,@EndIndex)+' Order By Row'

--	Print @cSql
	Exec (@cSql)
End